Skip Headers

Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 24 Using the Database Resource Manager

Oracle Database provides database resource management capability through its Database Resource Manager. This chapter introduces you to its use and contains the following topics:

- What Is the Database Resource Manager?

- Administering the Database Resource Manager

- Creating a Simple Resource Plan

- Creating Complex Resource Plans

- Managing Resource Consumer Groups

- Enabling the Database Resource Manager

- Putting It All Together: Database Resource Manager Examples

- Monitoring and Tuning the Database Resource Manager

- Interaction with Operating-System Resource Control

- Viewing Database Resource Manager Information

  Note: This chapter discusses the use of the Oracle-supplied `DBMS_RESOURCE_MANAGER` and `DBMS_RESOURCE_MANAGER_PRIVS` packages to administer the Database Resource Manager. An easier way to administer the Database Resource Manager is with the graphical user interface of Enterprise Manager.

## What Is the Database Resource Manager?

The main goal of the Database Resource Manager is to give the Oracle Database server more control over resource management decisions, thus circumventing problems resulting from inefficient operating system management.

### What Problems Does the Database Resource Manager Address?

When database resource allocation decisions are left to the operating system, you may encounter the following problems:

- Excessive overhead

  Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.

- Inefficient scheduling

  The operating system deschedules database servers while they hold latches, which is inefficient.

- Inappropriate allocation of resources

  The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

- Inability to manage database-specific resources, such as parallel execution servers and active sessions

### How Does the Database Resource Manager Address These Problems?

The Oracle Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated.

Specifically, using the Database Resource Manager, you can:

- Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users

- Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational on-line analytical processing) applications than to batch jobs.

- Limit the degree of parallelism of any operation performed by members of a group of users

- Create an active session pool. This pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate.

- Allow automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.

- Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit

- Create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.

- Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.

- Configure an instance to use a particular method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.

- Allow the cancellation of long-running SQL statements and the termination of long-running sessions.

### What Are the Elements of the Database Resource Manager?

The elements of database resource management, which you define through the Database Resource Manager packages, are described below.

Element | Description
--- | ---
Resource consumer group | User sessions grouped together based on resource processing requirements.
Resource plan | Contains directives that specify how resources are allocated to resource consumer groups.
Resource allocation method | The method/policy used by the Database Resource Manager when allocating for a particular resource; used by resource consumer groups and resource plans. The database provides the resource allocation methods that are available, but you determine which method to use.
Resource plan directive | Used by administrators to associate resource consumer groups with particular plans and allocate resources among resource consumer groups.

You will learn how to create and use these elements in later sections of this chapter.

### Understanding Resource Plans

This section briefly introduces the concept of resource plans. Included are some illustrations of simple resource plans. More complex plans are included in the examples presented later ("Putting It All Together: Database Resource Manager Examples"), after it has been explained how to build and maintain the elements of the Database Resource Manager.

Resource plans specify the resource consumer groups belonging to the plan and contain directives for how resources are to be allocated among these groups. You use the `DBMS_RESOURCE_MANAGER` package to create and maintain these elements of the Database Resource Manager: resource plans, resource consumer groups, and resource plan directives. Plan information is stored in tables in the data dictionary. Several views are available for viewing plan data.

See Also: "Viewing Database Resource Manager Information"

#### A Single-Level Resource Plan

The first illustration, shown in Figure 24-1, is of a single-level plan, where the plan allocates resources among resource consumer groups. The Great Bread Company has a plan called `GREAT_BREAD` that allocates CPU resources among three resource consumer groups. Specifically, `SALES` is allotted 60% of the CPU time, `MARKET` is allotted 20%, and `DEVELOP` receives the remaining 20%.

Figure 24-1 A Simple Resource Management Plan

Description of the illustration admin028.gif

Oracle Database provides a procedure (`CREATE_SIMPLE_PLAN`) that enables you to quickly create a simple resource plan. This procedure is discussed in "Creating a Simple Resource Plan".

#### A Multilevel Resource Plan

In addition to containing resource consumer groups, a plan can contain subplans. Perhaps the Great Bread Company chooses to divide their CPU resource as shown in Figure 24-2. The figure illustrates a plan schema, which contains a top plan (`GREAT_BREAD`) and all of its descendents.

Figure 24-2 A Multilevel Plan With Subplans

Description of the illustration admin027.gif

In this case, the `GREAT_BREAD` plan still allocates 20% of CPU resources to the consumer group `MARKET`. However, now it allocates CPU resources to subplans `SALES_TEAM` (60%), which in turn divides its share equally between the `WHOLESALE` and `RETAIL` groups, and `DEVELOP_TEAM` (20%), which in turn divides its resources equally between the `BREAD` and `MUFFIN` groups.

It is possible for a subplan or consumer group to have more than one parent (owning plan), but there cannot be any loops in a plan schema. An example of a subplan having more that one parent would be if the Great Bread Company had a night plan and a day plan. Both the night plan and the day plan contain the `sales` subplan as a member, but perhaps with a different CPU resource allocation in each instance.

Note: As explained in subsequent sections, the plans described should also contain a plan directive for `OTHER_GROUPS`. To present a simplified view, however, this plan directive is not shown.

#### Resource Consumer Groups

Resource consumer groups are groups of users, or sessions, that are grouped together based on their processing needs. Resource plan directives, discussed next, specify how resources are allocated among consumer groups and subplans in a plan schema.

#### Resource Plan Directives

How resources are allocated to resource consumer groups is specified in resource allocation directives. The Database Resource Manager provides several means of allocating resources.

##### CPU Method

This method enables you to specify how CPU resources are to be allocated among consumer groups or subplans. The multiple levels of CPU resource allocation (up to eight levels) provide a means of prioritizing CPU usage within a plan schema. Level 2 gets resources only after level 1 is unable to use all of its resources. Multiple levels not only provide a way of prioritizing, but they provide a way of explicitly specifying how all primary and leftover resources are to be used.

##### Active Session Pool with Queuing

You can control the maximum number of concurrently active sessions allowed within a consumer group. This maximum designates the active session pool. When a session cannot be initiated because the pool is full, the session is placed into a queue. When an active session completes, the first session in the queue can then be scheduled for execution. You can also specify a timeout period after which a job in the execution queue (waiting for execution) will timeout, causing it to terminate with an error.

An entire parallel execution session is counted as one active session.

##### Degree of Parallelism Limit

Specifying a parallel degree limit enables you to control the maximum degree of parallelism for any operation within a consumer group.

##### Automatic Consumer Group Switching

This method enables you to control resources by specifying criteria that, if met, causes the automatic switching of sessions to another consumer group. The criteria used to determine switching are:

- Switch group: specifies the consumer group to which this session is switched if the other (following) criteria are met

- Switch time: specifies the length of time that a session can execute before it is switched to another consumer group

- Switch time in call: specifies the length of time that a session can execute before it is switched to another consumer group. Once the top call finishes, the session is restored to its original consumer group.

- Use estimate: specifies whether the database is to use its own estimate of how long an operation will execute

The Database Resource Manager switches a running session to switch group if the session is active for more than switch time seconds. Active means that the session is running and consuming resources, not waiting idly for user input or waiting for CPU cycles. The session is allowed to continue running, even if the active session pool for the new group is full. Under these conditions a consumer group can have more sessions running than specified by its active session pool. Once the session finishes its operation and becomes idle, it is switched back to its original group.

If use estimate is set to `TRUE`, the Database Resource Manager uses a predicted estimate of how long the operation will take to complete. If the database estimate is longer than the value specified as the switch time, then the database switches the session before execution starts. If this parameter is not set, the operation starts normally and only switches groups when other switch criteria are met.

Switch time in call is useful for three-tier applications where the middle tier server is using session pooling. At the end of every top call, a session is switched back to its original consumer group--that is, the group it would be in had it just logged in. A top call in PL/SQL is an entire PL/SQL block being treated as one call. A top call in SQL is an individual SQL statement issued separately by the client being treated as a one call.

You cannot specify both switch time in call and switch time.

##### Canceling SQL and Terminating Sessions

You can also specify directives to cancel long-running SQL queries or to terminate long-running sessions. You specify this by setting `CANCEL_SQL` or `KILL_SESSION` as the switch group.

##### Execution Time Limit

You can specify a maximum execution time allowed for an operation. If the database estimates that an operation will run longer than the specified maximum execution time, the operation is terminated with an error. This error can be trapped and the operation rescheduled.

##### Undo Pool

You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current DML statement generating the redo is terminated. No other members of the consumer group can perform further data manipulation until undo space is freed from the pool.

##### Idle Time Limit

You can specify an amount of time that a session can be idle, after which it will be terminated. You can further restrict such termination to only sessions that are blocking other sessions.

## Administering the Database Resource Manager

You must have the system privilege `ADMINISTER_RESOURCE_MANAGER` to administer the Database Resource Manager. Typically, database administrators have this privilege with the `ADMIN` option as part of the DBA (or equivalent) role.

Being an administrator for the Database Resource Manager lets you execute all of the procedures in the `DBMS_RESOURCE_MANAGER` package. These are listed in the following table, and their use is explained in succeeding sections of this chapter.

Procedure | Description
--- | ---
`CREATE_SIMPLE_PLAN` | Creates a simple resource plan, containing up to eight consumer groups, in one step. This is the quickest way to get started when you use this package.
`CREATE_PLAN` | Creates a resource plan and specifies its allocation methods.
`UPDATE_PLAN` | Updates a resource plan.
`DELETE_PLAN` | Deletes a resource plan and its directives.
`DELETE_PLAN_CASCADE` | Deletes a resource plan and all of its descendents.
`CREATE_CONSUMER_GROUP` | Creates a resource consumer group.
`UPDATE_CONSUMER_GROUP` | Updates a consumer group.
`DELETE_CONSUMER_GROUP` | Deletes a consumer group.
`CREATE_PLAN_DIRECTIVE` | Specifies the resource plan directives that allocate resources to resource consumer groups within a plan or among subplans in a multilevel plan schema.
`UPDATE_PLAN_DIRECTIVE` | Updates plan directives
`DELETE_PLAN_DIRECTIVE` | Deletes plan directives
`CREATE_PENDING_AREA` | Creates a pending area (scratch area) within which changes can be made to a plan schema
`VALIDATE_PENDING_AREA` | Validates the pending changes to a plan schema
`CLEAR_PENDING_AREA` | Clears all pending changes from the pending area
`SUBMIT_PENDING_AREA` | Submits all changes for a plan schema
`SET_INITIAL_CONSUMER_GROUP` | Sets the initial consumer group for a user. This procedure has been deprecated. The database recommends that you use the `SET_CONSUMER_GROUP_MAPPING` procedure to specify the initial consumer group.
`SWITCH_CONSUMER_GROUP_FOR_SESS` | Switches the consumer group of a specific session
`SWITCH_CONSUMER_GROUP_FOR_USER` | Switches the consumer group of all sessions belonging to a specific user
`SET_CONSUMER_GROUP_MAPPING` | Maps sessions to consumer groups
`SET_CONSUMER_GROUP_MAPPING_PRI` | Establishes session attribute mapping priorities

You may, as an administrator with the `ADMIN` option, choose to grant the administrative privilege to other users or roles. This is possible using the `DBMS_RESOURCE_MANAGER_PRIVS` package. This package contains the procedures listed in the table below.

Procedure | Description
--- | ---
`GRANT_SYSTEM_PRIVILEGE` | Grants `ADMINISTER_RESOURCE_MANAGER` system privilege to a user or role.
`REVOKE_SYSTEM_PRIVILEGE` | Revokes `ADMINISTER_RESOURCE_MANAGER` system privilege from a user or role.
`GRANT_SWITCH_CONSUMER_GROUP` | Grants permission to a user, role, or `PUBLIC` to switch to a specified resource consumer group.
`REVOKE_SWITCH_CONSUMER_GROUP` | Revokes permission for a user, role, or `PUBLIC` to switch to a specified resource consumer group.

The following example grants the administrative privilege to user `scott`, but does not grant `scott` the `ADMIN` option. Therefore, `scott` can execute all of the procedures in the `DBMS_RESOURCE_MANAGER` package, but `scott` cannot use the `GRANT_SYSTEM_PRIVILEGE` procedure to grant the administrative privilege to others.

    EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE -
        (GRANTEE_NAME => 'scott', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', -
         ADMIN_OPTION => FALSE);

You can revoke this privilege using the `REVOKE_SYSTEM_PRVILEGE` procedure.

Note: The `ADMINISTER_RESOURCE_MANAGER` system privilege can only be granted or revoked by using the `DBMS_RESOURCE_MANAGER_PRIVS` package. It cannot be granted or revoked through the SQL `GRANT` or `REVOKE` statements.

The other procedures in the `DBMS_RESOURCE_MANAGER_PRIVS` package are discussed in "Managing the Switch Privilege".

See Also: PL/SQL Packages and Types Reference. contains detailed information about the Database Resource Manager packages: `DBMS_RESOURCE_MANAGER` `DBMS_RESOURCE_MANAGER_PRIVS`

## Creating a Simple Resource Plan

You can quickly create a simple resource plan that will be adequate for many situations using the `CREATE_SIMPLE_PLAN` procedure. This procedure enables you to create consumer groups and allocate resources to them by executing a single statement. Using this procedure, you are not required to invoke the procedures that are described in succeeding sections for creating a pending area, creating each consumer group individually, and specifying resource plan directives.

You can specify the following parameters for the `CREATE_SIMPLE_PLAN` procedure:

Parameter | Description
--- | ---
`SIMPLE_PLAN` | Name of the plan
`CONSUMER_GROUP1` | Consumer group name for first group
`GROUP1_CPU` | CPU resource allocated to this group
`CONSUMER_GROUP2` | Consumer group name for second group
`GROUP2_CPU` | CPU resource allocated to this group
`CONSUMER_GROUP3` | Consumer group name for third group
`GROUP3_CPU` | CPU resource allocated to this group
`CONSUMER_GROUP4` | Consumer group name for fourth group
`GROUP4_CPU` | CPU resource allocated to this group
`CONSUMER_GROUP5` | Consumer group name for fifth group
`GROUP5_CPU` | CPU resource allocated to this group
`CONSUMER_GROUP6` | Consumer group name for sixth group
`GROUP6_CPU` | CPU resource allocated to this group
`CONSUMER_GROUP7` | Consumer group name for seventh group
`GROUP7_CPU` | CPU resource allocated to this group
`CONSUMER_GROUP8` | Consumer group name for eighth group
`GROUP8_CPU` | CPU resource allocated to this group

Up to eight consumer groups can be specified using this procedure and the only plan directive that can be specified is for CPU. The plan uses the `EMPHASIS` CPU allocation policy and each consumer group uses the `ROUND_ROBIN` scheduling policy.Each consumer group specified in the plan is allocated its CPU percentage at level 2. Also included in the plan are `SYS_GROUP` (a system-defined group that is the initial consumer group for the users `SYS` and `SYSTEM`) and `OTHER_GROUPS`.

Example: Using the CREATE_SIMPLE_PLAN Procedure

    BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
       CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 80,
       CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 20);
    END;

Executing the preceding statements creates the following plan:

Consumer Group | Level 1 | Level 2 | Level 3
--- | --- | --- | ---
`SYS_GROUP` | 100% | - | -
`mygroup1` | - | 80% | -
`mygroup2` | - | 20% | -
`OTHER_GROUPS` | - | - | 100%

## Creating Complex Resource Plans

This section describes the actions and `DBMS_RESOURCE_MANAGER` procedures that you can use when your situation requires that you create more complex resource plans. It contains the following sections:

- Using the Pending Area for Creating Plan Schemas

- Creating Resource Plans

- Creating Resource Consumer Groups

- Specifying Resource Plan Directives

### Using the Pending Area for Creating Plan Schemas

The first thing you must do to create or modify plan schemas is to create a pending area. This is a scratch area allowing you to stage your changes and to validate them before they are made active.

#### Creating a Pending Area

To create a pending area, you use the following statement:

    EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

In effect, you are making the pending area active and "loading" all existing, or active, plan schemas into the pending area so that they can be updated or new plans added. Active plan schemas are those schemas already stored in the data dictionary for use by the Database Resource Manager. If you attempt to update a plan or add a new plan without first activating (creating) the pending area, you will receive an error message notifying you that the pending area is not active.

Views are available for inspecting all active resource plan schemas as well as the pending ones. These views are listed in Viewing Database Resource Manager Information.

#### Validating Changes

At any time when you are making changes in the pending area you can call the validate procedure as shown here.

    EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

This procedure checks whether changes that have been made are valid. The following rules must be adhered to, and are checked by the validate procedure:

1. No plan schema can contain any loops.

2. All plans and resource consumer groups referred to by plan directives must exist.

3. All plans must have plan directives that point to either plans or resource consumer groups.

4. All percentages in any given level must not add up to greater than 100.

5. A plan that is currently being used as a top plan by an active instance cannot be deleted.

6. The following plan directive parameters can appear only in plan directives that refer to resource consumer groups (not other resource plans):

   - `PARALLEL_DEGREE_LIMIT_P1`

   - `ACTIVE_SESS_POOL_P1`

   - `QUEUEING_P1`

   - `SWITCH_GROUP`

   - `SWITCH_TIME`

   - `SWITCH_ESTIMATE`

   - `MAX_EST_EXEC_TIME`

   - `UNDO_POOL`

   - `MAX_IDLE_TIME`

   - `MAX_IDLE_BLOCKER_TIME`

   - `SWITCH_TIME_IN_CALL`

7. There can be no more than 32 resource consumer groups in any active plan schema. Also, at most, a plan can have 32 children.

8. Plans and resource consumer groups cannot have the same name.

9. There must be a plan directive for `OTHER_GROUPS` somewhere in any active plan schema. This ensures that a session which is not part of any of the consumer groups included in the currently active plan is allocated resources (as specified by the `OTHER_GROUPS` directive).

You will receive an error message if any of the preceding rules are violated. You can then make changes to fix any problems and call the validate procedure again.

It is possible to create "orphan" consumer groups that have no plan directives referring to them. This allows the creation of consumer groups that will not currently be used, but may be part of some plan to be implemented in the future.

#### Submitting Changes

After you have validated your changes, call the submit procedure to make your changes active.

    EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

The submit procedure also performs validation, so you do not necessarily need to make separate calls to the validate procedure. However, if you are making major changes to plan schemas, debugging problems is often easier if you incrementally validate your changes. No changes are submitted (made active) until validation is successful on all of the changes in the pending area.

The `SUBMIT_PENDING_AREA` procedure clears (deactivates) the pending area after successfully validating and committing the changes.

Note: A call to `SUBMIT_PENDING_AREA` may fail even if `VALIDATE_PENDING_AREA` succeeds. This can happen if, for example, a plan being deleted is loaded by an instance after a call to `VALIDATE_PENDING_AREA`, but before a call to `SUBMIT_PENDING_AREA`.

#### Clearing the Pending Area

There is also a procedure for clearing the pending area at any time. This statement causes all of your changes to be cleared from the pending area:

    EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

You must call the `CREATE_PENDING_AREA` procedure before you can again attempt to make changes.

### Creating Resource Plans

When you create a resource plan, you can specify the parameters shown in the following table. The first parameter is required; the remainder are optional.

Parameter | Description
--- | ---
`PLAN` | Name of the plan.
`COMMENT` | Any descriptive comment.
`CPU_MTH` | Resource allocation method for specifying how much CPU each consumer group or subplan gets. `EMPHASIS`, the default method, is for multilevel plans that use percentages to specify how CPU is distributed among consumer groups. `RATIO` is for single-level plans that use ratios to specify how CPU is distributed.
`ACTIVE_SESS_POOL_MTH` | Active session pool resource allocation method. Limits the number of active sessions. All other sessions are inactive and wait in a queue to be activated. `ACTIVE_SESS_POOL_ABSOLUTE` is the default and only method available.
`PARALLEL_DEGREE_LIMIT_MTH` | Resource allocation method for specifying a limit on the degree of parallelism of any operation. `PARALLEL_DEGREE_LIMIT_ABSOLUTE` is the default and only method available.
`QUEUEING_MTH` | Queuing resource allocation method. Controls order in which queued inactive sessions will execute. `FIFO_TIMEOUT` is the default and only method available.

Oracle Database provides one resource plan, `SYSTEM_PLAN`, that contains a simple structure that may be adequate for some environments. It is illustrated later in "An Oracle-Supplied Plan".

#### Creating a Plan

You create a plan using the `CREATE_PLAN` procedure. The following creates a plan called `great_bread`. You choose to use the default resource allocation methods.

    EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'great_bread', -
        COMMENT => 'great plan');

#### Updating a Plan

Use the `UPDATE_PLAN` procedure to update plan information. If you do not specify the arguments for the `UPDATE_PLAN` procedure, they remain unchanged in the data dictionary. The following statement updates the `COMMENT` parameter.

    EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'great_bread', -
         NEW_COMMENT => 'great plan for great bread');

#### Deleting a Plan

The `DELETE_PLAN` procedure deletes the specified plan as well as all the plan directives associated with it. The following statement deletes the `great_bread` plan and its directives.

    EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'great_bread');

The resource consumer groups themselves are not deleted, but they are no longer associated with the `great_bread` plan.

The `DELETE_PLAN_CASCADE` procedure deletes the specified plan as well as all its descendants (plan directives, subplans, resource consumer groups). If `DELETE_PLAN_CASCADE` encounters an error, it will roll back, leaving the plan schema unchanged.

#### Using the Ratio Policy

the `RATIO` policy is a single-level CPU allocation method. Instead of percentages, you specify numbers corresponding to the ratio of CPU you want to give to the consumer group. For example, given three consumer groups `GOLD_CG`, `SILVER_CG`, and `BRONZE_CG`, assume that we specify the following plan directives:

    DBMS_RESOURCE_MANAGER.CREATE_PLAN
       (PLAN => 'service_level_plan',
        CPU_MTH -> 'RATIO',
        COMMENT => 'service level plan');
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
       (PLAN => 'service_level_plan',
        GROUP_OR_SUBPLAN => 'GOLD_CG',
        COMMENT => 'Gold service level customers',
        CPU_P1 => 10);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
       (PLAN => 'service_level_plan',
        GROUP_OR_SUBPLAN => 'SILVER_CG',
        COMMENT => 'Silver service level customers',
        CPU_P1 => 5);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
       (PLAN => 'service_level_plan',
        GROUP_OR_SUBPLAN => 'BRONZE_CG',
        COMMENT => 'Bonze service level customers',
        CPU_P1 => 2);DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
        (PLAN => 'service_level_plan',
        GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
        COMMENT => 'Lowest priority sessions',
        CPU_P1 => 1);

The ratio of CPU allocation would be 10:5:2:1 for the `GOLD_CG`, `SILVER_CG`, `BRONZE_CG`, and `OTHER_GROUPS` consumer groups, respectively.

If sessions exists only in the `GOLD_CG` and `SILVER_CG` consumer groups, then the ratio of CPU allocation would be 10:5 between the two groups.

### Creating Resource Consumer Groups

When you create a resource consumer group, you can specify the following parameters:

Parameter | Description
--- | ---
`CONSUMER_GROUP` | Name of the consumer group.
`COMMENT` | Any comment.
`CPU_MTH` | The resource allocation method for distributing CPU among sessions in the consumer group. The default is `ROUND_ROBIN`, which uses a round-robin scheduler to ensure that sessions are fairly executed. `RUN_TO_COMPLETION` specifies that sessions with the largest active time are scheduled ahead of other sessions.

There are two special consumer groups that are always present in the data dictionary, and they cannot be modified or deleted. These are:

- `DEFAULT_CONSUMER_GROUP`

  This is the initial consumer group for all users/sessions that have not been explicitly assigned an initial consumer group. `DEFAULT_CONSUMER_GROUP` has switch privileges granted to `PUBLIC`; therefore, all users are automatically granted switch privilege for this consumer group (see "Managing the Switch Privilege").

- `OTHER_GROUPS`

  This consumer group cannot be explicitly assigned to a user. `OTHER_GROUPS` must have a resource directive specified in the schema of any active plan. This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema, including `DEFAULT_CONSUMER_GROUP`.

Additionally, two other groups, `SYS_GROUP` and `LOW_GROUP`, are provided as part of the Oracle-supplied `SYSTEM_PLAN` that is described in "An Oracle-Supplied Plan".

#### Creating a Consumer Group

You create a consumer group using the `CREATE_CONSUMER_GROUP` procedure. The following creates a consumer group called `sales`. Remember, the pending area must be active to execute this statement successfully.

    EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'sales', -
        COMMENT => 'retail and wholesale sales');

#### Updating a Consumer Group

Use the `UPDATE_CONSUMER_GROUP` procedure to update consumer group information. If you do not specify the arguments for the `UPDATE_CONSUMER_GROUP` procedure, they remain unchanged in the data dictionary.

#### Deleting a Consumer Group

The `DELETE_CONSUMER_GROUP` procedure deletes the specified consumer group. Upon deletion of a consumer group, all users having the deleted group as their initial consumer group will have the `DEFAULT_CONSUMER_GROUP` set as their initial consumer group. All currently running sessions belonging to a deleted consumer group will be switched to `DEFAULT_CONSUMER_GROUP`.

### Specifying Resource Plan Directives

Resource plan directives assign consumer groups to resource plans and provide the parameters for each resource allocation method. When you create a resource plan directive, you can specify the following parameters

Parameter | Description
--- | ---
`PLAN` | Name of the resource plan.
`GROUP_OR_SUBPLAN` | Name of the consumer group or subplan.
`COMMENT` | Any comment.
`CPU_P1` | For `EMPHASIS`, specifies the CPU percentage at the first level. For `RATIO`, specifies the weight of CPU usage. Default is `NULL` for all `CPU` parameters.
`CPU_P2` | For `EMPHASIS`, specifies CPU percentage at the second level. Not applicable for `RATIO`.
`CPU_P3` | For `EMPHASIS`, specifies CPU percentage at the third level. Not applicable for `RATIO`.
`CPU_P4` | For `EMPHASIS`, specifies CPU percentage at the fourth level. Not applicable for `RATIO`.
`CPU_P5` | For `EMPHASIS`, specifies CPU percentage at the fifth level. Not applicable for `RATIO`.
`CPU_P6` | For `EMPHASIS`, specifies CPU percentage at the sixth level. Not applicable for `RATIO`.
`CPU_P7` | For `EMPHASIS`, specifies CPU percentage at the seventh level. Not applicable for `RATIO`.
`CPU_P8` | For `EMPHASIS`, specifies CPU percentage at the eighth level. Not applicable for `RATIO`.
`ACTIVE_SESS_POOL_P1` | Specifies maximum number of concurrently active sessions for a consumer group. Default is `UNLIMITED`.
`QUEUEING_P1` | Specified time (in seconds) after which a job in an inactive session queue (waiting for execution) will time out. Default is `UNLIMITED`.
`PARALLEL_DEGREE_LIMIT_P1` | Specifies a limit on the degree of parallelism for any operation. Default is `UNLIMITED`.
`SWITCH_GROUP` | Specifies consumer group to which this session is switched if other switch criteria are met. If the group name is '`CANCEL_SQL`', then the current call will be canceled when other switch criteria are met. If the group name is '`KILL_SESSION`', then the session will be killed when other switch criteria are met. Default is `NULL`.
`SWITCH_TIME` | Specifies time (in seconds) that a session can execute before an action is taken. Default in `UNLIMITED`. You cannot specify both `SWITCH_TIME` and `SWITCH_TIME_IN_CALL`.
`SWITCH_ESTIMATE` | If `TRUE`, tells the database to use its execution time estimate to automatically switch the consumer group of an operation prior to beginning its execution. Default is `FALSE`.
`MAX_EST_EXEC_TIME` | Specifies the maximum execution time (in seconds) allowed for a session. If the optimizer estimates that an operation will take longer than `MAX_EST_EXEC_TIME`, the operation is not started and `ORA-07455` is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is `UNLIMITED`.
`UNDO_POOL` | Sets a maximum in kilobytes (K) on the total amount of undo generated by a consumer group. Default is `UNLIMITED`.
`MAX_IDLE_TIME` | Indicates the maximum session idle time. Default is `NULL`, which implies unlimited.
`MAX_IDLE_BLOCKER_TIME` | Indicates the maximum session idle time of a blocking session. Default is `NULL`, which implies unlimited.
`SWITCH_TIME_IN_CALL` | Specifies the time (in seconds) that a session can execute before an action is taken. At the end of the call, the consumer group of the session is restored to its original consumer group. Default is `UNLIMITED`. You cannot specify both `SWITCH_TIME_IN_CALL` and `SWITCH_TIME`.

#### Creating a Resource Plan Directive

You use the `CREATE_PLAN_DIRECTIVE` to create a resource plan directive. The following statement creates a resource plan directive for plan `great_bread`.

    EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread', -
         GROUP_OR_SUBPLAN => 'sales', COMMENT => 'sales group', -
         CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 => 4);

To complete the plan, similar to that shown in Figure 24-1, execute the following statements:

    BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread',
         GROUP_OR_SUBPLAN => 'market', COMMENT => 'marketing group',
         CPU_P1 => 20);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread',
         GROUP_OR_SUBPLAN => 'develop', COMMENT => 'development group',
         CPU_P1 => 20);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'great_bread',
         GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'this one is required',
         CPU_P1 => 0, CPU_P2 => 100);
    END;

In this plan, consumer group `sales` has a maximum degree of parallelism of 4 for any operation, while none of the other consumer groups are limited in their degree of parallelism. Also, whenever there are leftover level 1 CPU resources, they are allocated (100%) to `OTHER_GROUPS`.

#### Updating Resource Plan Directives

Use the `UPDATE_PLAN_DIRECTIVE` procedure to update plan directives. This example changes CPU allocation for resource consumer group `develop`.

    EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'great_bread', -
         GROUP_OR_SUBPLAN => 'develop', NEW_CPU_P1 => 15);

If you do not specify the arguments for the `UPDATE_PLAN_DIRECTIVE` procedure, they remain unchanged in the data dictionary.

#### Deleting Resource Plan Directives

To delete a resource plan directive, use the `DELETE_PLAN_DIRECTIVE` procedure

#### How Resource Plan Directives Interact

If there are multiple resource plan directives that refer to the same consumer group, then the following rules apply for specific cases:

1. The parallel degree limit for the consumer group will be the minimum of all the incoming values.

2. The active session pool for the consumer group will be the sum of all the incoming values and the queue timeout will be the minimum of all incoming timeout values.

3. If there is more than one switch group and more than one switch time, the Database Resource Manager will choose the most restrictive of all incoming values. Specifically:

   - `SWITCH_TIME =` `min` (all incoming over_switch_time values)

   - `SWITCH_ESTIMATE = TRUE` overrides `SWITCH_ESTIMATE = FALSE`

     Notes: If both plan directives specify the same switch time, but different switch groups, then the choice as to which group to switch to is statically but arbitrarily decided by the Database Resource Manager. You cannot specify both `SWITCH_TIME` and `SWITCH_TIME_IN_CALL` plan directives for a single consumer group.

4. If a session is switched to another consumer group because it exceeds its switch time, that session will execute even if the active session pool for the new consumer group is full.

5. The maximum estimated execution time will be the most restrictive of all incoming values. Specifically:

   `MAX_EST_EXEC_TIME = min` (all incoming `MAX_EST_EXEC_TIME` values)

## Managing Resource Consumer Groups

Before you enable the Database Resource Manager, you must assign resource consumer groups to users. This can be done manually, or you can provide mappings that enable the database to automatically assign user sessions to consumer groups depending upon session attributes.

In addition to providing procedures to create, update, or delete the elements used by the Database Resource Manager, the `DBMS_RESOURCE_MANAGER` package contains procedures that effect the assigning of resource consumer groups to users. It also provides procedures that allow you to temporarily switch a user session to another consumer group.

The `DBMS_RESOURCE_MANAGER_PRIVS` package, described earlier for granting the Database Resource Manager system privilege, can also be used to grant the switch privilege to another user, who can then alter their own consumer group.

Of the procedures discussed in this section, you use a pending area only for the `SET_CONSUMER_GROUP_MAPPING` and `SET_CONSUMER_GROUP_MAPPING_PRI` procedures. These procedures are used for the automatic assigning of sessions to consumer groups.

This section contains the following topics:

- Assigning an Initial Resource Consumer Group

- Changing Resource Consumer Groups

- Managing the Switch Privilege

- Automatically Assigning Resource Consumer Groups to Sessions

### Assigning an Initial Resource Consumer Group

The initial consumer group of a session is determined by mapping the attributes of the session to a consumer group. For more information on how to configure the mapping, see the section "Automatically Assigning Resource Consumer Groups to Sessions".

### Changing Resource Consumer Groups

There are two procedures, which are part of the `DBMS_RESOURCE_MANAGER` package, that allow administrators to change the resource consumer group of running sessions. Both of these procedures can also change the consumer group of any parallel execution server sessions associated with the coordinator session. The changes made by these procedures pertain to current sessions only; they are not persistent. They also do not change the initial consumer groups for users.

Instead of killing a session of a user who is using excessive CPU, an administrator can instead change that user's consumer group to one that is allowed less CPU. Or, this switching can be enforced automatically, using automatic consumer group switching resource plan directives.

#### Switching a Session

The `SWITCH_CONSUMMER_GROUP_FOR_SESS` causes the specified session to immediately be moved into the specified resource consumer group. In effect, this statement can raise or lower priority. The following statement changes the resource consumer group of a specific session to a new consumer group. The session identifier (`SID`) is 17, the session serial number (`SERIAL#`) is 12345, and the session is to be changed to the `high_priority` consumer group.

    EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345', -
       'high_priorty');

The `SID`, session serial number, and current resource consumer group for a session are viewable using the `V$SESSION` data dictionary view.

#### Switching Sessions for a User

The `SWITCH_CONSUMER_GROUP_FOR_USER` procedure changes the resource consumer group for all sessions with a given user name.

    EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('scott', -
        'low_group');

#### Using the DBMS_SESSION Package to Switch Consumer Group

If granted the switch privilege, users can switch their current consumer group using the `SWITCH_CURRENT_CONSUMER_GROUP` procedure in the `DBMS_SESSION` package.

This procedure enables users to switch to a consumer group for which they have the switch privilege. If the caller is another procedure, then this procedure enables users to switch to a consumer group for which the owner of that procedure has switch privileges.

The parameters for this procedure are:

Parameter | Description
--- | ---
`NEW_CONSUMER_GROUP` | The consumer group to which the user is switching.
`OLD_CONSUMER_GROUP` | An output parameter. Stores the name of the consumer group from which the user switched. Can be used to switch back later.
`INITIAL_GROUP_ON_ERROR` | Controls behavior if a switching error occurs. If `TRUE`, in the event of an error, the user is switched to the initial consumer group. If `FALSE`, raise an error.

The following example illustrates switching to a new consumer group. By printing the value of the output parameter `old_group`, we illustrate how the old consumer group name has been saved.

    SET serveroutput on
    DECLARE
        old_group varchar2(30);
    BEGIN
    DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('sales', old_group, FALSE);
    DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group);
    END;

The following line is output:

    OLD GROUP = DEFAULT_CONSUMER_GROUP

The `DBMS_SESSION` package can be used from within a PL/SQL application, thus allowing the application to change consumer groups, or effectively priority, dynamically.

Note that the Database Resource Manager considers a switch to have taken place even if the `SWITCH_CURRENT_CONSUMER_GROUP` procedure is called to switch the session to the consumer group that it is already in.

Note: The Database Resource Manager also works in environments where a generic database user name is used to log on to an application. The `DBMS_SESSION` package can be called to switch the consumer group assignment of a session at session startup, or as particular modules are called.

See Also: PL/SQL Packages and Types Reference for additional examples and more information about the `DBMS_SESSION` package

### Managing the Switch Privilege

Using the `DBMS_RESOURCE_MANAGER_PRIVS` package, you can grant or revoke the switch privilege to a user, role, or `PUBLIC`. The switch privilege gives users the privilege to switch their current resource consumer group to a specified resource consumer group. The package also enables you to revoke the switch privilege.

The actual switching is done by executing a procedure in the `DBMS_SESSION` package. A user who has been granted the switch privilege (or a procedure owned by that user) can use the `SWITCH_CURRENT_CONSUMER_GROUP` procedure to switch to another resource consumer group. The new group must be one to which the user has been specifically authorized to switch.

#### Granting the Switch Privilege

The following example grants the privilege to switch to a consumer group. User `scott` is granted the privilege to switch to consumer group `bug_batch_group`.

    EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('scott', -
         'bug_batch_group', TRUE);

User `scott` is also granted permission to grant switch privileges for `bug_batch_group` to others.

If you grant a user permission to switch to a particular consumer group, then that user can switch their current consumer group to the new consumer group.

If you grant a role permission to switch to a particular resource consumer group, then any users who have been granted that role and have enabled that role can immediately switch their current consumer group to the new consumer group.

If you grant `PUBLIC` the permission to switch to a particular consumer group, then any user can switch to that group.

If the `GRANT_OPTION` argument is `TRUE`, then users granted switch privilege for the consumer group can also grant switch privileges for that consumer group to others.

#### Revoking Switch Privileges

The following example revokes user `scott`'s privilege to switch to consumer group `bug_batch_group`.

    EXEC DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP ('scott', -
         'bug_batch_group');

If you revoke a user's switch privileges to a particular consumer group, then any subsequent attempts by that user to switch to that consumer group will fail. If you revoke the initial consumer group from a user, then that user will automatically be part of the `DEFAULT_CONSUMER_GROUP` when logging in.

If you revoke from a role the switch privileges to a consumer group, then any users who only had switch privilege for the consumer group through that role will not be able to subsequently switch to that consumer group.

If you revoke switch privileges to a consumer group from `PUBLIC`, then any users other than those who are explicitly assigned switch privileges either directly or through `PUBLIC`, will not be able to subsequently switch to that consumer group.

### Automatically Assigning Resource Consumer Groups to Sessions

You can configure the Database Resource Manager to automatically assign consumer groups to sessions by providing mappings between session attributes and consumer groups. Further, you can prioritize the mappings so as to indicate which mapping has precedence in case of conflicts.

There are two types of session attributes: login attributes and runtime attributes. The login attributes are meaningful only at session login time, when the Database Resource Manager determines the initial consumer group of the session. In contrast, a session that has already logged in can later be reassigned to another consumer group based on its run-time attributes.

You use the `SET_CONSUMER_GROUP_MAPPING` and `SET_CONSUMER_GROUP_MAPPING_PRI` procedures to configure the automatic assigning of sessions to consumer groups. You must use a pending area for these procedures.

#### Creating Consumer Group Mappings

The mapping for a session consists of a set of attribute/consumer group pairs that determine how a session is matched to a consumer group. You use the `SET_CONSUMER_GROUP_MAPPING` procedure to map a single session attribute to a consumer group. The parameters for this procedure are:

Parameter | Description
--- | ---
`ATTRIBUTE` | The login or runtime session attribute type
`VALUE` | The value of the attribute being mapped
`CONSUMER_GROUP` | The consumer group to map to.

The attribute can be one of the following:

Attribute | Type | Description
--- | --- | ---
`ORACLE_USER` | Login | The Oracle Database user name
`SERVICE_NAME` | Login | The service name used by the client to establish a connection
`CLIENT_OS_USER` | Login | The operating system user name of the client that is logging in
`CLIENT_PROGRAM` | Login | The name of the client program used to log into the server
`CLIENT_MACHINE` | Login | The name of the machine from which the client is making the connection
`MODULE_NAME` | Runtime | The module name in the application currently executing as set by the `DBMS_APPLICATION_INFO.SET_MODULE` procedure or the equivalent OCI attribute setting
`MODULE_NAME_ACTION` | Runtime | A combination of the current module and the action being performed as set by either of the following procedures or their equivalent OCI attribute setting: `DBMS_APPLICATION_INFO.SET_MODULE` `DBMS_APPLICATION_INFO.SET_ACTION` The attribute is specified as the module name followed by a period (.), followed by the action name (`module_name.action_name`).
`SERVICE_MODULE` | Runtime | A combination of service and module names in this form: `service_name.module_name`
`SERVICE_MODULE_ACTION` | Runtime | A combination of service name, module name, and action name, in this form: `service_name.module_name.action_name`

For example, the following statement causes user `scott` to map to the `dev_group` consumer group every time he logs in:

    BEGIN
    DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
         (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'scott', 'dev_group');
    END;

#### Creating Attribute Mapping Priorities

To resolve conflicting mappings, you can establish a priority ordering of the attributes from most important to least important. You use the `SET_CONSUMER_GROUP_MAPPING_PRI` procedure to set the priority of each attribute to a unique integer from 1 (most important) to 10 (least important). The following example illustrates this setting of priorities:

    BEGIN
    DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
        EXPLICIT => 1,    SERVICE_MODULE_ACTION => 2,    SERVICE_MODULE => 3,    MODULE_NAME_ACTION => 4,    MODULE_NAME => 5,    SERVICE_NAME => 6,    ORACLE_USER => 7,    CLIENT_PROGRAM => 8,    CLIENT_OS_USER => 9,    CLIENT_MACHINE => 10);
    END;

In this example, the priority of the database user name is set to 7 (less important), while the priority of the module name is set to 5 (more important).

Note: `SET_CONSUMER_GROUP_MAPPING_PRI` requires that you include the pseudo-attribute `EXPLICIT` as an argument. It must be set to 1. It indicates that explicit consumer group switches have the highest priority. You explicitly switch consumer groups with these package procedures, which are described in detail in PL/SQL Packages and Types Reference: `DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP` `DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS` `DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER`

To illustrate how mapping priorities work, assume that in addition to the mapping of user `scott` to the `dev_group` consumer group, there is also a module name mapping as follows:

    EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING -
         (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'backup', 'low_priority');

Now if the session sets its module name to `backup`, the session would be reassigned to the `low_priority` consumer group, because module name mapping has a higher priority than database user mapping.

To prevent unauthorized clients from setting their session attributes so that they map to higher priority consumer groups, user switch privileges for consumer groups are enforced. This means that even though the attribute of a given session matches a mapping pair, the mapping is only considered valid if the session has the switching privilege for that particular consumer group. Sessions are automatically switched only to consumer groups for which they have been granted switch privileges.

#### Automatic Group Switching

Each session can be switched automatically to another consumer group via the mappings at distinct points in time:

- When the session first logs in, the mapping is evaluated to determine the initial group of the session.

- If the current mapping attribute of a session is A, then if the attribute A is set to a new value (only possible for runtime attributes) then the mapping is reevaluated and the session is switched to the appropriate consumer group.

- If a runtime session attribute is modified such that the current mapping becomes a different attribute B, then the session is switched.

- Whenever the client ID is set to a different value, the mapping is reevaluated and the session is switched.

Two things to note about the preceding rules are:

- If a runtime attribute for which a mapping is provided is set to the same value it already has, or if the client ID is set to the same value it already has, then no switching takes place.

- A session can be switched to the same consumer group it is already in. The effect of switching in this case is to zero out session statistics that are typically zeroed out during a switch to a different group (for example, the `ACTIVE_TIME_IN_GROUP` value of the session).

## Enabling the Database Resource Manager

You enable the Database Resource Manager by setting the `RESOURCE_MANAGER_PLAN` initialization parameter. This parameter specifies the top plan, identifying the plan schema to be used for this instance. If no plan is specified with this parameter, the Database Resource Manager is not activated.

Note: The Resource Manager automatically activates if a Scheduler window that specifies a resource plan opens.

The following example activates the Database Resource Manager and assigns the top plan as `mydb_plan`.

    RESOURCE_MANAGER_PLAN = mydb_plan

You can also activate or deactivate the Database Resource Manager, or change the current top plan, using the `DBMS_RESOURCE_MANAGER.SWITCH_PLAN` package procedure or the `ALTER SYSTEM` statement. In this example, the top plan is specified as `mydb_plan`:

    ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';

An error message is returned if the specified plan does not exist in the data dictionary.

To deactivate the Database Resource Manager, issue the following statement:

    ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

The Scheduler can automatically change the Resource Manager plan at Scheduler window boundaries. In some cases, this may be unacceptable. For example, if you have an important task to finish, and if you set the Resource Manager plan to give your task priority, then you expect that the plan will remain the same until you change it. However, because a Scheduler window could become activated after you have set your plan, the Resource Manager plan may change while your task is running.

To prevent this situation, you can set the `RESOURCE_MANAGER_PLAN` parameter to the name of the plan you want for the system and prepend the name with "`FORCE:`". Using the prefix `FORCE` indicates that the current Resource Manager plan can be changed only when the database administrator changes the value of the `RESOURCE_MANAGER_PLAN` parameter. This restriction can be lifted by reexecuting the command without prepending the plan name with "`FORCE:`".

    ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';

The `DBMS_RESOURCE_MANAGER.SWITCH_PLAN` package procedure has a similar capability.

See Also: PL/SQL Packages and Types Reference for more information on `DBMS_RESOURCE_MANAGER.SWITCH_PLAN`.

## Putting It All Together: Database Resource Manager Examples

This section provides some examples of resource plan schemas. The following examples are presented:

- Multilevel Schema Example

- Example of Using Several Resource Allocation Methods

- An Oracle-Supplied Plan

### Multilevel Schema Example

The following statements create a multilevel schema as illustrated in Figure 24-3. They use default plan and resource consumer group methods.

    BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan',
       COMMENT => 'Resource plan/method for bug users sessions');
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan',
       COMMENT => 'Resource plan/method for mail users sessions');
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan',
       COMMENT => 'Resource plan/method for bug and mail users sessions');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Online_group',
       COMMENT => 'Resource consumer group/method for online bug users sessions');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Batch_group',
       COMMENT => 'Resource consumer group/method for batch job bug users sessions');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maintenance_group',
       COMMENT => 'Resource consumer group/method for users sessions for bug db maint');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_users_group',
       COMMENT => 'Resource consumer group/method for mail users sessions');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Postman_group',
       COMMENT => 'Resource consumer group/method for mail postman');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maintenance_group',
       COMMENT => 'Resource consumer group/method for users sessions for mail db maint');
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
       GROUP_OR_SUBPLAN => 'Bug_Online_group',
       COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
       PARALLEL_DEGREE_LIMIT_P1 => 8);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
       GROUP_OR_SUBPLAN => 'Bug_Batch_group',
       COMMENT => 'batch bug users sessions at level 1', CPU_P1 => 20, CPU_P2 => 0,
       PARALLEL_DEGREE_LIMIT_P1 => 2);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
       GROUP_OR_SUBPLAN => 'Bug_Maintenance_group',
       COMMENT => 'bug maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 100,
       PARALLEL_DEGREE_LIMIT_P1 => 3);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
       GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
       COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
       CPU_P3 => 100);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
       GROUP_OR_SUBPLAN => 'Mail_Postman_group',
       COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0,
       PARALLEL_DEGREE_LIMIT_P1 => 4);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
       GROUP_OR_SUBPLAN => 'Mail_users_group',
       COMMENT => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80,
       PARALLEL_DEGREE_LIMIT_P1 => 4);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
       GROUP_OR_SUBPLAN => 'Mail_Maintenance_group',
       COMMENT => 'mail maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 20,
       PARALLEL_DEGREE_LIMIT_P1 => 2);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
       GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
       COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
       CPU_P3 => 100);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
       GROUP_OR_SUBPLAN => 'maildb_plan',
       COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
       GROUP_OR_SUBPLAN => 'bugdb_plan',
       COMMENT => 'all bug users sessions at level 1', CPU_P1 => 70);
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    END;

The preceding call to `VALIDATE_PENDING_AREA` is optional because the validation is implicitly performed in `SUBMIT_PENDING_AREA`.

Figure 24-3 Multilevel Schema

Description of the illustration admin057.gif

### Example of Using Several Resource Allocation Methods

The example presented here could represent a plan for a database supporting a packaged ERP (Enterprise Resource Planning) or CRM (Customer Relationship Management). The work in such an environment can be highly varied. There may be a mix of short transactions and quick queries, in combination with longer running batch jobs that include large parallel queries. The goal is to give good response time to OLTP (Online Transaction Processing), while allowing batch jobs to run in parallel.

The plan is summarized in the following table.

Group | CPU Resource Allocation % | Active Session Pool Parameters | Automatic Switching Parameters | Maximum Estimated Execution Time | Undo Pool
--- | --- | --- | --- | --- | ---
`oltp` | Level 1: 80% |  | Switch to group: `batch` Switch time: 3 Use estimate: `TRUE` | -- | Size: 200K
`batch` | Level 2: 100% | Pool size: 5 Timeout: 600 | -- | Time: 3600 | --
`OTHER_GROUPS` | Level 3: 100% | -- | -- | -- | --

The following statements create the preceding plan, which is named `erp_plan`:

    BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'erp_plan',
      COMMENT => 'Resource plan/method for ERP Database');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp',
      COMMENT => 'Resource consumer group/method for OLTP jobs');
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch',
      COMMENT => 'Resource consumer group/method for BATCH jobs');
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
      GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80,
      SWITCH_GROUP => 'batch', SWITCH_TIME => 3,SWITCH_ESTIMATE => TRUE,
      UNDO_POOL => 200);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
      GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 100,
      ACTIVE_SESS_POOL_P1 => 5, QUEUEING_P1 => 600,
      MAX_EST_EXEC_TIME => 3600);
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan',
      GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P3 => 100);
    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    END;

### An Oracle-Supplied Plan

Oracle Database provides one default resource manager plan, `SYSTEM_PLAN`, which gives priority to system sessions. `SYSTEM_PLAN` is defined as follows:

Resource Consumer Group | CPU Resource Allocation
--- | ---
Level 1 | Level 2 | Level 3
`SYS_GROUP` | 100% | 0% | 0%
`OTHER_GROUPS` | 0% | 100% | 0%
`LOW_GROUP` | 0% | 0% | 100%

The database-provided groups in this plan are:

- `SYS_GROUP` is the initial consumer group for the users `SYS` and `SYSTEM`.

- `OTHER_GROUPS` applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema.

- `LOW_GROUP` provides a group having lower priority than `SYS_GROUP` and `OTHER_GROUPS` in this plan. It is up to you to decide which user sessions will be part of `LOW_GROUP`. Switch privilege is granted to `PUBLIC` for this group.

These groups can be used, or not used, and can be modified or deleted.

You can use this simple database-supplied plan if it is appropriate for your environment.

## Monitoring and Tuning the Database Resource Manager

To effectively monitor and tune the Database Resource Manager, you must design a representative environment. The Database Resource Manager works best in large production environments in which system utilization is high. If a test places insufficient load on the system, measured CPU allocations can be very different from the allocations specified in the active resource plan. This is because the Database Resource Manager does not attempt to enforce CPU allocation percentage limits as long as consumer groups are getting the resources they need.

### Creating the Environment

To create a representative environment, there must be sufficient load (demand for CPU resources) to make CPU resources scarce. If the following rules are followed, the test environment should generate actual (measured) resource allocations that match those specified in the active resource plan.

1. Create the minimum number of concurrently running processes required to generate sufficient load. This is the larger of:

   - Four processes for each consumer group

   - 1.5 * (number of processors) for each consumer group. If the result is not an integer, round up.

2. Each and every process must be capable of consuming all of the CPU resources allocated to the consumer group in which it runs. Write resource intensive programs that continue to spin no matter what happens. This can be as simple as:

       BEGIN
       DECLARE
           m NUMBER;
         BEGIN
           FOR i IN 1..100000 LOOP
             FOR j IN 1..100000 LOOP
               /*         * The following query does a cartesian product without         * a predicate and takes up significant CPU time.         */         select count(*) into m from v$sysstat, v$system_event;
             END LOOP;
           END LOOP;
         END;
       END;
       /

### Why Is This Necessary to Produce Expected Results?

When every group can secure as much CPU resources as it demands, the Database Resource Manager first seeks to maximize system throughput, not to enforce allocation percentages. For example, consider the following conditions:

- There is only one process available to run for each consumer group.

- Each process runs continuously.

- There are four CPUs.

In this case, the measured CPU allocation to each consumer group will be 25%, no matter what the allocations specified in the active resource plan.

Another factor determines the calculation in (1) in the preceding section. Processor affinity scheduling at the operating system level can distort CPU allocation on underutilized systems. This is explained in the following paragraphs.

Until the number of concurrently running processes reaches a certain level, typical operating system scheduling algorithms will prevent full utilization. The Database Resource Manager controls CPU usage by restricting the number of running processes. By deciding which processes are allowed to run and for what duration, the Database Resource Manager controls CPU resource allocation. When a CPU has resources available, and other processors are fully utilized, the operating system migrates processes to the underutilized processor, but not immediately.

With processor affinity, the operating system waits (for a time) to migrate processes, "hoping" that another process will be dispatched to run instead of forcing process migration from one CPU to another. On a fully loaded system with enough processes waiting, this strategy will work. In large production environments, processor affinity increases performance significantly, because invalidating the current CPU cache and then loading the new one is quite expensive. Since processes have processor affinity on most platforms, more processes than CPUs for each consumer group must be run. Otherwise, full system utilization is not possible.

### Monitoring Results

Use these views to help you monitor the results of your Resource Manager settings.

- `V$RSRC_CONSUMER_GROUP`

- `V$RSRC_SESSION_INFO`

- `V$RSRC_PLAN_HISTORY`

V$RSRC_CONSUMER_GROUP Use the `V$RSRC_CONSUMER_GROUP` view to monitor CPU usage. It provides the cumulative amount of CPU time consumed by all sessions in each consumer group. It also provides a number of other measures helpful for tuning.

    SQL> SELECT NAME, CONSUMED_CPU_TIME FROM V$RSRC_CONSUMER_GROUP;

    NAME                             CONSUMED_CPU_TIME
    -------------------------------- -----------------
    OTHER_GROUPS                                 14301
    TEST_GROUP                                    8802
    TEST_GROUP2                                      0

    3 rows selected.

V$RSRC_SESSION_INFO Use this view to monitor the status of a particular session. The view shows how the session has been affected by the Resource Manager. It provides information such as:

- The consumer group that the session currently belongs to

- The consumer group that the session originally belonged to

- The session attribute that was used to map the session to the consumer group

- Session state (`RUNNING`, `WAIT_FOR_CPU`, `QUEUED`, and so on)

- Current and cumulative statistics for metrics, such as CPU consumed, wait times, and queued time

    SQL> SELECT se.sid sess_id, co.name consumer_group,
     se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
     FROM v$rsrc_session_info se, v$rsrc_consumer_group co
     WHERE se.current_consumer_group_id = co.id

    SESS_ID CONSUMER_GROUP  STATE      CPU_TIME CPU_WAIT_TIME QUEUED_TIME
    ------- --------------- -------- ---------- ------------- -----------
        145 SYS_GROUP       RUNNING      572217             0           0
        158 OTHER_GROUPS    WAITING         220             0           0
        142 OTHER_GROUPS    WAITING      146453             0           0
        141 OTHER_GROUPS    WAITING      148228             0           0

V$RSRC_PLAN_HISTORY This view shows when resource manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time. For each entry in the view, the `V$RSRC_CONS_GROUP_HISTORY` view has a corresponding entry for each consumer group in the plan that shows the cumulative statistics for the consumer group.

See Also: Oracle Database Reference for information on these and other Resource Manager views.

## Interaction with Operating-System Resource Control

The Oracle Database server expects a static configuration and allocates internal resources, such as latches, from available resources detected at database startup. The database might not perform optimally and can become unstable if resource configuration changes very frequently.

### Guidelines for Using Operating-System Resource Control

If you do choose to use Operating-system resource control with Oracle Database, then it should be used judiciously, according to the following guidelines:

1. Operating-system resource control should not be used concurrently with the Database Resource Manager, because neither of them are aware of each other's existence. As a result, both the operating system and Database Resource Manager try to control resource allocation in a manner that causes unpredictable behavior and instability of Oracle Database.

   - If you want to control resource distribution within an instance, use the Database Resource Manager and turn off operating-system resource control.

   - If you have multiple instances on a node and you want to distribute resources among them, use operating-system resource control, not the Database Resource Manager.

     Note: Oracle Database currently does not support the use of both tools simultaneously. Future releases might allow for their interaction on a limited scale.

2. In an Oracle Database environment, the use of an operating-system resource manager, such as Hewlett Packard's Process Resource Manager (PRM) or Sun's Solaris Resource Manager (SRM), is supported only if all of the following conditions are met:

   - Each instance must be assigned to a dedicated operating-system resource manager group or managed entity.

   - The dedicated entity running all the instance's processes must run at one priority (or resource consumption) level.

   - Process priority management must not be enabled.

     Caution: Management of individual database processes at different priority levels (for example, using the `nice` command on UNIX platforms) is not supported. Severe consequences, including instance crashes, can result. You can expect similar undesirable results if operating-system resource control is permitted to manage memory on which an Oracle Database instance is pinned.

3. If you chose to use operating-system resource control, make sure you turn off the Database Resource Manager. By default, the Database Resource Manager is turned off. If it is not, you can turn it off by issuing the following statement:

       ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='';

   Also remember to reset this parameter in your initialization parameter file, so that the Database Resource Manager is not activated the next time the database is started up.

### Dynamic Reconfiguration

Tools such as Sun's processor sets and dynamic system domains work well with an Oracle Database. There is no need to restart an instance if the number of CPUs changes.

The database dynamically detects any change in the number of available CPUs and reallocates internal resources. On most platforms, the database automatically adjusts the value of the `CPU_COUNT` initialization parameter to the number of available CPUs.

## Viewing Database Resource Manager Information

The following table lists views that are associated with Database Resource Manager:

View | Description
--- | ---
`DBA_RSRC_CONSUMER_GROUP_PRIVS` `USER_RSRC_CONSUMER_GROUP_PRIVS` | `DBA` view lists all resource consumer groups and the users and roles to which they have been granted. `USER` view lists all resource consumer groups granted to the user.
`DBA_RSRC_CONSUMER_GROUPS` | Lists all resource consumer groups that exist in the database.
`DBA_RSRC_MANAGER_SYSTEM_PRIVS` `USER_RSRC_MANAGER_SYSTEM_PRIVS` | `DBA` view lists all users and roles that have been granted Database Resource Manager system privileges. `USER` view lists all the users that are granted system privileges for the `DBMS_RESOURCE_MANAGER` package.
`DBA_RSRC_PLAN_DIRECTIVES` | Lists all resource plan directives that exist in the database.
`DBA_RSRC_PLANS` | Lists all resource plans that exist in the database.
`DBA_RSRC_GROUP_MAPPINGS` | Lists all of the various mapping pairs for all of the session attributes
`DBA_RSRC_MAPPING_PRIORITY` | Lists the current mapping priority of each attribute
`DBA_USERS` `USERS_USERS` | `DBA` view contains information about all users of the database. Specifically, for the Database Resource Manager, it contains the initial resource consumer group for the user. `USER` view contains information about the current user, and specifically, for the Database Resource Manager, it contains the current user's initial resource consumer group.
`V$ACTIVE_SESS_POOL_MTH` | Displays all available active session pool resource allocation methods.
`V$BLOCKING_QUIESCE` | Lists all sessions that could potentially block a quiesce operation. Includes sessions that are active and not in the `SYS_GROUP` consumer group.
`V$PARALLEL_DEGREE_LIMIT_MTH` | Displays all available parallel degree limit resource allocation methods.
`V$QUEUEING_MTH` | Displays all available queuing resource allocation methods.
`V$RSRC_CONS_GROUP_HISTORY` | For each entry in the view `V$RSRC_PLAN_HISTORY`, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.
`V$RSRC_CONSUMER_GROUP` | Displays information about active resource consumer groups. This view can be used for tuning.
`V$RSRC_CONSUMER_GROUP_CPU_MTH` | Displays all available CPU resource allocation methods for resource consumer groups.
`V$RSRC_PLAN` | Displays the names of all currently active resource plans.
`V$RSRC_PLAN_CPU_MTH` | Displays all available CPU resource allocation methods for resource plans.
`V$RSRC_PLAN_HISTORY` | Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.
`V$RSRC_SESSION_INFO` | Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.
`V$SESSION` | Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.

You can use these views for viewing privileges, viewing plan schemas, or you can monitor them to gather information for tuning the Database Resource Manager. Some examples of their use follow.

See Also: Oracle Database Reference for detailed information about the contents of each of these views

### Viewing Consumer Groups Granted to Users or Roles

The `DBA_RSRC_CONSUMER_GROUP_PRIVS` view displays the consumer groups granted to users or roles. Specifically, it displays the groups to which a user or role is allowed to belong or be switched. For example, in the view shown below, user `scott` can belong to the consumer groups `market` or `sales`, he has the ability to assign (grant) other users to the `sales` group but not the `market` group. Neither group is his initial consumer group.

    SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;

    GRANTEE                        GRANTED_GROUP                  GRA INI
    ------------------------------ ------------------------------ --- ---
    PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
    PUBLIC                         LOW_GROUP                      NO  NO
    SCOTT                          MARKET                         NO  NO
    SCOTT                          SALES                          YES NO
    SYSTEM                         SYS_GROUP                      NO  YES

Scott was granted the ability to switch to these groups using the `DBMS_RESOURCE_MANAGER_PRIVS` package.

### Viewing Plan Schema Information

This example shows using the `DBA_RSRC_PLANS` view to display all of the resource plans defined in the database. All of the plans displayed are active, meaning they are not staged in the pending area

    SQL> SELECT PLAN,COMMENTS,STATUS FROM DBA_RSRC_PLANS;

    PLAN         COMMENTS                                                 STATUS
    -----------  -------------------------------------------------------  ------
    SYSTEM_PLAN  Plan to give system sessions priority                    ACTIVE
    BUGDB_PLAN   Resource plan/method for bug users sessions              ACTIVE
    MAILDB_PLAN  Resource plan/method for mail users sessions             ACTIVE
    MYDB_PLAN    Resource plan/method for bug and mail users sessions     ACTIVE
    GREAT_BREAD  Great plan for great bread                               ACTIVE
    ERP_PLAN     Resource plan/method for ERP Database                    ACTIVE

    6 rows selected.

### Viewing Current Consumer Groups for Sessions

You can use the `V$SESSION` view to display the consumer groups that are currently assigned to sessions.

    SQL> SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION;

    SID    SERIAL#  USERNAME                  RESOURCE_CONSUMER_GROUP
    -----  -------  ------------------------  --------------------------------
    .
    .
    .
       11       136 SYS                       SYS_GROUP
       13     16570 SCOTT                     SALES

    10 rows selected.

### Viewing the Currently Active Plans

This example sets `mydb_plan`, as created by the statements shown earlier in "Multilevel Schema Example", as the top level plan. The `V$RSRC_PLAN` view is queried to display the currently active plans.

    SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;

    System altered.

    SQL> SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;

    NAME                            IS_TO
    -------------------------------------
    MYDB_PLAN                       TRUE
    MAILDB_PLAN                     FALSE
    BUGDB_PLAN                      FALSE